package com.yulian.web_api.report;

import java.io.BufferedReader;
import java.io.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;
import java.io.InputStreamReader;
import java.io.PrintStream;
import java.math.BigDecimal;
import java.text.SimpleDateFormat;
import java.time.LocalDate;
import java.time.format.DateTimeFormatter;
import java.util.*;

import io.vertx.core.json.JsonArray;
import io.vertx.ext.auth.User;
import org.apache.poi.openxml4j.opc.OPCPackage;
import org.apache.poi.openxml4j.opc.PackageAccess;

import com.yulian.repository.CommonRepositoryImpl;
import com.yulian.util.CommonUtils;
import com.yulian.util.XLSX2CSV;

import io.netty.util.internal.logging.InternalLogger;
import io.netty.util.internal.logging.Log4JLoggerFactory;
import io.vertx.core.CompositeFuture;
import io.vertx.core.Future;
import io.vertx.core.Promise;
import io.vertx.core.Vertx;
import io.vertx.core.json.JsonObject;
import io.vertx.pgclient.PgPool;
import io.vertx.sqlclient.Row;
import io.vertx.sqlclient.RowSet;
import io.vertx.sqlclient.Tuple;

//导入期初库存批次明细
public class HandleInitialBatchDetial {

    final InternalLogger logger = Log4JLoggerFactory.getInstance(HandleInitialBatchDetial.class);
    CommonRepositoryImpl commonRepository = new CommonRepositoryImpl();

    public Future<JsonObject> handleExcelFile(Vertx vertx, PgPool pool, JsonObject config, String file_name, User user) {
        // 最后返回给客户端的 json 对象
        Promise<JsonObject> promise = Promise.promise();
        JsonObject responseJSObj = new JsonObject();
        responseJSObj.put("msg", "处理Excel文件结果:");
        String create_user2 = user.attributes().getJsonObject("accessToken").getString("username");
        long create_user = Long.parseLong(create_user2);
        this.commonRepository.getCon(pool)
        .onSuccess(conn -> {
            /*** 处理上传的excel
             * 利用OPCPackage 打开文件
             */
            int minColumns = -1;
            BufferedReader bReader;
            //2. read data from excel
            try (OPCPackage p = OPCPackage.open(file_name, PackageAccess.READ)) {
                ByteArrayOutputStream baos = new ByteArrayOutputStream();
                PrintStream out = new PrintStream(baos);
                //read the excel to csv
                XLSX2CSV xlsx2csv = new XLSX2CSV(p, out, minColumns);
                xlsx2csv.process();
                ByteArrayInputStream in = new ByteArrayInputStream(baos.toByteArray());
                bReader = new BufferedReader(new InputStreamReader(in));

                String line = "";
                //忽略前三行
                for (int i = 0; i < 3; i++) {
                    line = bReader.readLine();
                }
                //统计记录总数
                int count = 0;
                boolean item_not_null_valid = true;
                List<String> errMsgList = new ArrayList<String>();
                List<Tuple> initial_batch_detial_batch = new ArrayList<>();

                @SuppressWarnings("rawtypes")
                List<Future> futures = new ArrayList<Future>();

                while ((line = bReader.readLine()) != null) {
                    item_not_null_valid = true;
                    if (line.trim() != "") {
                        count = count + 1;
                        String[] data = line.split(":");
                        // 获取各个字段的值
                        String warehouse_name = "";
                        if (!data[0].toString().equals("")) {
                            warehouse_name = data[0].toString();
                            //0校验仓库名的合法性
                            String get_id_sql = "select distinct id from warehouse where warehouse_name = $1 and is_delete=0";
                            Future<JsonObject> checkWarehouseNameFuture = this.commonRepository.getIdByNameFuture2(conn, get_id_sql, warehouse_name, "仓库名", count, "initial_batch_detial_check_warehouse_name");
                            futures.add(checkWarehouseNameFuture);
                        } else {
                            errMsgList.add("第" + count + "行的仓库名为空");
                            item_not_null_valid = false;
                        }
                        String supply_chain_goods_name = "";
                        if (!data[1].toString().equals("")) {
                            supply_chain_goods_name = data[1].toString();
                            //1校验供应链商品名的合法性
                            String get_id_sql = "select distinct id from supply_chain_goods where supply_chain_goods_name = $1 and is_delete=0";
                            Future<JsonObject> checkSupplyChainGoodsNameFuture = this.commonRepository.getIdByNameFuture2(conn, get_id_sql, supply_chain_goods_name, "供应链商品名", count, "initial_batch_detial_check_supply_chain_goods_name");
                            futures.add(checkSupplyChainGoodsNameFuture);
                        } else {
                            errMsgList.add("第" + count + "行的供应链商品名为空");
                            item_not_null_valid = false;
                        }
                        //供应链商品结算包规
                        double supply_chain_goods_settle_package_spec = 0;
                        if (!data[2].toString().equals("") && this.commonRepository.isNumericA(data[2].toString())) {
                            supply_chain_goods_settle_package_spec = Double.parseDouble(data[2].toString());
                            //2校验供应链商品结算包规的合法性(是否为数字)
                        } else {
                            errMsgList.add("第" + count + "行的供应链商品结算包规为空");
                            item_not_null_valid = false;
                        }
                        //商品批次号
                        String goods_batch_code = "";
                        if (!data[3].toString().equals("") && this.commonRepository.isNumericA(data[3].toString())) {
                            goods_batch_code = data[3].toString();
                            //3校验商品批次号的合法性(是否为数字)
                        } else {
                            errMsgList.add("第" + count + "行的商品批次号为空");
                            item_not_null_valid = false;
                        }

                        String supplier_name = "";
                        if (!data[4].toString().equals("")) {
                            supplier_name = data[4].toString();
                            //4校验供应商名的合法性
                            String get_id_sql = "select distinct id from supplier_info where supplier_name = $1 and is_delete=0";
                            Future<JsonObject> checkLegalEntityFuture = this.commonRepository.getIdByNameFuture2(conn, get_id_sql, supplier_name, "供应商名", count, "initial_batch_detial_check_supplier_name");
                            futures.add(checkLegalEntityFuture);
                        } else {
                            errMsgList.add("第" + count + "行的供应商名为空");
                            item_not_null_valid = false;
                        }

                        //实际成交斤价
                        double real_settlement_price = 0;
                        if (!data[5].toString().equals("") && this.commonRepository.isNumericA(data[5].toString())) {
                            real_settlement_price = Double.parseDouble(data[5].toString());
                            //5校验供应链商品结算包规的合法性(是否为数字)
                        } else {
                            errMsgList.add("第" + count + "行的实际成交斤价为空");
                            item_not_null_valid = false;
                        }

                        //运费斤价
                        double transport_price = 0;
                        if (!data[6].toString().equals("") && this.commonRepository.isNumericA(data[6].toString())) {
                            transport_price = Double.parseDouble(data[6].toString());
                            //6校验运费斤价的合法性(是否为数字)
                        } else {
                            errMsgList.add("第" + count + "行的运费斤价为空");
                            item_not_null_valid = false;
                        }

                        //到岸斤价
                        double land_price = 0;
                        if (!data[7].toString().equals("") && this.commonRepository.isNumericA(data[7].toString())) {
                            land_price = Double.parseDouble(data[7].toString());
                            //7校验到岸斤价的合法性(是否为数字)
                        } else {
                            errMsgList.add("第" + count + "行的到岸斤价为空");
                            item_not_null_valid = false;
                        }
                        //库存件数
                        double inventory_number = 0;
                        if (!data[8].toString().equals("") && this.commonRepository.isNumericA(data[8].toString())) {
                            inventory_number = Double.parseDouble(data[8].toString());
                            //8校验库存件数的合法性(是否为数字)
                        } else {
                            errMsgList.add("第" + count + "行的库存件数为空");
                            item_not_null_valid = false;
                        }
                        //库存重量
                        double inventory_weight = 0;
                        if (!data[9].toString().equals("") && this.commonRepository.isNumericA(data[9].toString())) {
                            inventory_weight = Double.parseDouble(data[9].toString());
                            //9校验库存重量的合法性(是否为数字)
                        } else {
                            errMsgList.add("第" + count + "行的库存重量为空");
                            item_not_null_valid = false;
                        }
                        //库存商品到岸金额
                        double inventory_land_acount = 0;
                        if (!data[10].toString().equals("") && this.commonRepository.isNumericA(data[10].toString())) {
                            inventory_land_acount = Double.parseDouble(data[10].toString());
                            //10校验库存商品到岸金额的合法性(是否为数字)
                        } else {
                            errMsgList.add("第" + count + "行的库存商品到岸金额为空");
                            item_not_null_valid = false;
                        }
                        //聚合商品名
                        String aggregation_name = "";
                        if (!data[11].toString().equals("")) {
                            aggregation_name = data[11];
                            //4校验聚合商品名的合法性
                            String get_id_sql = "select distinct id from aggregation_goods where aggregation_name = $1 and is_delete=0";
                            Future<JsonObject> checkLegalEntityFuture = this.commonRepository.getIdByNameFuture2(conn, get_id_sql, aggregation_name, "聚合商品名", count, "aggregation_goods_check_aggregation_name");
                            futures.add(checkLegalEntityFuture);
                        } else {
                            errMsgList.add("第" + count + "行的聚合商品名为空");
                            item_not_null_valid = false;
                        }

                        //通过非空校验,将该行数据暂存入initial_batch_detial_batch
                        if (item_not_null_valid) {
                            initial_batch_detial_batch.add(Tuple.of(warehouse_name, supply_chain_goods_name, supply_chain_goods_settle_package_spec, goods_batch_code, supplier_name, real_settlement_price, transport_price, land_price, inventory_number, inventory_weight, inventory_land_acount));
                        } else {
                            //若某一行数据非空检测结果不合法
                            responseJSObj.put("code",-1).put("msg", responseJSObj.getString("msg") + errMsgList.get(0));
                            //跳出循环
                            break;
                        }
                    } //end if (line.trim() != "") {  //读取一行数据结束
                } //end while((line = bReader.readLine()) != null){  //所有数据读取完毕
                //数据读取完毕后，关闭bReader
                if (bReader != null) {
                    bReader.close();
                }
                // 关闭OPCPackage
                p.close();

                /***************  读取数据完毕，通过数据非空校验 ******/
                if (item_not_null_valid) {
                    logger.info("通过非空校验,继续执行值校验, future个数: {}", futures.size());
                    //继续执行数据合法校验
                    CompositeFuture.join(futures).onComplete(ar2 -> {
                        if (ar2.succeeded()) {
                            JsonObject check_result = new JsonObject();
                            boolean check_value_valid = true;
                            //需要知道每一个future对应的第几行第几列，从而准确取出相应的字段的编码
                            int tmp_row_num = 0;
                            String[] row_result = new String[4];
                            String method = "insert";
                            List<Tuple> insert_data_batch = new ArrayList<>();
                            for (int i = 0; i < futures.size(); i++) {
                                check_result = (JsonObject) ar2.result().list().get(i);
                                //确定行数,从0开始 i = 1-9: row_num=0;  i = 10-18: row_num=1;
                                int row_num = i / 4;
                                //确定列数，从0开始
                                int col_num = i % 4;
                                if (row_num <= tmp_row_num) {
                                    if (check_result.getInteger("id") == -1) { //没有通过值校验
                                        check_value_valid = false;
                                        responseJSObj.put("code",-1).put("msg", responseJSObj.getString("msg") + "; " + check_result.getString("msg"));
                                        promise.complete(responseJSObj);
                                    } else {
                                        //通过校验,取出code存放在数组里
                                        row_result[col_num] = check_result.getString("data");
                                    }
                                    //当前项为一行的最后一列
                                    if (i == (tmp_row_num + 1) * 4 - 1) {
                                        // 处理当前一行数据;还原为一行一行数据进行处理
                                        Tuple tempTuple = initial_batch_detial_batch.get(tmp_row_num);
                                        if (method.equals("insert")) {
                                            long a= Long.parseLong(row_result[0]);
                                            long a1= Long.parseLong(row_result[1]);
                                            long a2= Long.parseLong(row_result[2]);
                                            long a3= Long.parseLong(row_result[3]);
                                            Date date = new Date();
                                            SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd");
                                            String nowDate=format.format(date);
                                            LocalDate localDate = LocalDate.parse(nowDate, DateTimeFormatter.ofPattern("yyyy-MM-dd"));
                                            insert_data_batch.add(Tuple.of(a, a1, tempTuple.getDouble(2), tempTuple.getString(3), a2, tempTuple.getDouble(5),
                                            tempTuple.getDouble(6), tempTuple.getDouble(7), tempTuple.getDouble(8), tempTuple.getDouble(9), tempTuple.getDouble(10),a3,localDate,create_user,create_user));
                                        }
                                    }
                                } else {//当前项为一行的第一列
                                    tmp_row_num = row_num;
                                    row_result = new String[4];
                                    //检查新的一行第一列的校验结果并存入row_result里
                                    if (check_result.getInteger("id") == -1) { //没有通过值校验
                                        check_value_valid = false;
                                        responseJSObj.put("code",-1).put("msg", responseJSObj.getString("msg") + "; " + check_result.getString("msg"));
                                        promise.complete(responseJSObj);
                                    } else { //通过校验
                                        row_result[col_num] = check_result.getString("data");
                                    }
                                }
                            }

                            /********** 通过全部数据合法性校验，准备进行写入或更新 *****/
                            if (check_value_valid) {
                                logger.info("通过全部值校验，准备进行写入或更新");
                                String warehouse_ids="";
                                String supply_chain_goods_ids="";
                                String supply_chain_goods_settle_package_specs="";
                                String goods_batch_codes="";
                                String supplier_ids="";
                                String aggregation_ids="";
                                String time_lines="";
                                //插入之前先判断有没有存在的存在则执行更新sql
                                for(int i =0 ; i< insert_data_batch.size();i++){
                                    if (i==insert_data_batch.size()-1){
                                        warehouse_ids=warehouse_ids+insert_data_batch.get(i).getLong(0);
                                        supply_chain_goods_ids=supply_chain_goods_ids+insert_data_batch.get(i).getLong(1);
                                        supply_chain_goods_settle_package_specs=supply_chain_goods_settle_package_specs+insert_data_batch.get(i).getDouble(2);
                                        goods_batch_codes=goods_batch_codes+"'"+insert_data_batch.get(i).getString(3)+"'";
                                        supplier_ids=supplier_ids+insert_data_batch.get(i).getLong(4);
                                        aggregation_ids=aggregation_ids+insert_data_batch.get(i).getLong(11);
                                        time_lines=time_lines+"'"+insert_data_batch.get(i).getLocalDate(12)+"'";
                                    }else {
                                        warehouse_ids=warehouse_ids+insert_data_batch.get(i).getLong(0)+",";
                                        supply_chain_goods_ids=supply_chain_goods_ids+insert_data_batch.get(i).getLong(1)+",";
                                        supply_chain_goods_settle_package_specs=supply_chain_goods_settle_package_specs+insert_data_batch.get(i).getDouble(2)+",";
                                        goods_batch_codes=goods_batch_codes+"'"+insert_data_batch.get(i).getString(3)+" ',";
                                        supplier_ids=supplier_ids+insert_data_batch.get(i).getLong(4)+",";
                                        aggregation_ids=aggregation_ids+insert_data_batch.get(i).getLong(11)+",";
                                        DateTimeFormatter df = DateTimeFormatter.ofPattern("yyyy-MM-dd");
                                        LocalDate localDate = insert_data_batch.get(i).getLocalDate(12);
                                                time_lines=time_lines+"'"+localDate.format(df)+"',";
                                    }
                                }
                                List<Future> futureList = new ArrayList<>();
                                String count_sql = "select aggregation_id,warehouse_id,supply_chain_goods_settle_package_spec,time_line " +
                                        "from supplier_batch_goods_psi " +
                                        "where is_delete=0 " +
                                        "and warehouse_id in ("+warehouse_ids+") " +
                                        "and supply_chain_goods_settle_package_spec in ("+supply_chain_goods_settle_package_specs+") " +
                                        "and aggregation_id in ("+aggregation_ids+") " +
                                        "and time_line in ("+time_lines+") ";
                                Future<JsonArray>  future2 = this.commonRepository.queryJsonArray(conn, count_sql, "getSupplierBatchGoodsPsiRecords");
                                futureList.add(future2);
                                String select_sql="select warehouse_id,supply_chain_goods_id,supply_chain_goods_settle_package_spec,supplier_id,goods_batch_code,time_line " +
                                        "from initial_batch_detail " +
                                        "where is_delete=0 " +
                                        "and warehouse_id in ("+warehouse_ids+") " +
                                        "and supply_chain_goods_id in ("+supply_chain_goods_ids+") " +
                                        "and goods_batch_code in ("+goods_batch_codes+") " +
                                        "and supplier_id in ("+supplier_ids+") " +
                                        "and time_line in ("+time_lines+") " +
                                        "and supply_chain_goods_settle_package_spec in ("+supply_chain_goods_settle_package_specs+") ";
                                Future<JsonArray>  future1 = this.commonRepository.queryJsonArray(conn, select_sql, "getInitialBatchDetailRecords");
                                futureList.add(future1);
                                CompositeFuture.join(futureList).onComplete(all1 -> {
                                                if (all1.succeeded()){
                                                    JsonArray resJSArray=(JsonArray)all1.result().list().get(1);
                                                    JsonArray resJSArray2=(JsonArray)all1.result().list().get(0);

                                                    List<Future> futures2 = new ArrayList<Future>();
                                                    List<Tuple> new_insert_data_batch = new ArrayList<Tuple>();
                                                    List<Tuple> new_insert_data_batch2 = new ArrayList<Tuple>();
                                                    List<Map<String,Object>> mapList = new ArrayList<>();
                                                    List<Tuple> update_data_batch = new ArrayList<Tuple>();
                                                    if (resJSArray2.size()>0){
                                                        //有存在的数据则过滤出来 不存在 的数据新增
                                                        for (int r = 0 ; r<insert_data_batch.size();r++){
                                                            Integer warehouse_id = Math.toIntExact(insert_data_batch.get(r).getLong(0));
                                                            Integer aggregation_id = Math.toIntExact(insert_data_batch.get(r).getLong(11));
                                                            LocalDate localDate = insert_data_batch.get(r).getLocalDate(12);
                                                            BigDecimal bigDecimal2 =new BigDecimal(insert_data_batch.get(r).getDouble(2));
                                                            boolean exist = false;
                                                            for (int i = 0; i < resJSArray2.size(); i++) {
                                                                JsonObject jsonObj = resJSArray2.getJsonObject(i);
                                                                Integer warehouse_id2 = Integer.valueOf(jsonObj.getString("warehouse_id"));
                                                                String time_line_str =jsonObj.getString("time_line");
                                                                DateTimeFormatter df = DateTimeFormatter.ofPattern("yyyy-MM-dd");
                                                                LocalDate localDate2 = LocalDate.parse(time_line_str, df);
                                                                int eq = localDate.compareTo(localDate2);
                                                                double supply_chain_goods_settle_package_spec = Double.parseDouble(jsonObj.getString("supply_chain_goods_settle_package_spec"));
                                                                BigDecimal bigDecimal1 =new BigDecimal(supply_chain_goods_settle_package_spec);
                                                                Integer aggregation_id2 = Integer.valueOf(jsonObj.getString("aggregation_id"));
                                                                if (aggregation_id2==aggregation_id && warehouse_id2==warehouse_id && eq==0 && bigDecimal1.compareTo(bigDecimal2)==0 ){
                                                                    //该存在的数据不需要新增
                                                                    exist=true;
                                                                    break;
                                                                }
                                                            }
                                                            if (!exist){
                                                                //不存在 数据库则需要判断是否在新增数据集合里面
                                                                if (mapList.size()==0){
                                                                    Map<String,Object> map =new HashMap<>();
                                                                    map.put("warehouse_id",warehouse_id);
                                                                    map.put("aggregation_id",aggregation_id);
                                                                    map.put("localDate",localDate);
                                                                    map.put("supply_chain_goods_settle_package_spec",insert_data_batch.get(r).getDouble(2));
                                                                    new_insert_data_batch2.add(Tuple.of(warehouse_id,aggregation_id,localDate,insert_data_batch.get(r).getDouble(2),create_user,create_user));
                                                                }else {
                                                                    boolean have = false;
                                                                    for (int u = 0 ; u < mapList.size(); u++){
                                                                        Map<String,Object> map = mapList.get(u);
                                                                        Integer warehouse_id3 =(Integer) map.get("warehouse_id");
                                                                        Integer aggregation_id3 =(Integer) map.get("aggregation_id");
                                                                        LocalDate localDate3 =(LocalDate) map.get("localDate");
                                                                        int eq = localDate3.compareTo(localDate3);
                                                                        double supply_chain_goods_settle_package_spec3 =(double)map.get("supply_chain_goods_settle_package_spec");
                                                                        BigDecimal bigDecimal3 =new BigDecimal(supply_chain_goods_settle_package_spec3);
                                                                        BigDecimal bigDecimal4 =new BigDecimal(insert_data_batch.get(r).getDouble(2));
                                                                        if (warehouse_id3==warehouse_id && aggregation_id3==aggregation_id  && bigDecimal3.compareTo(bigDecimal4)==0
                                                                        && eq==0){
                                                                            //有数据则不add
                                                                            have=true;
                                                                            break;
                                                                        }
                                                                    }
                                                                    if (!have){
                                                                        //不存在头的新增数据集合里面则可以add
                                                                        Map<String,Object> map =new HashMap<>();
                                                                        map.put("warehouse_id",warehouse_id);
                                                                        map.put("aggregation_id",aggregation_id);
                                                                        map.put("localDate",localDate);
                                                                        map.put("supply_chain_goods_settle_package_spec",insert_data_batch.get(r).getDouble(2));
                                                                        new_insert_data_batch2.add(Tuple.of(warehouse_id,aggregation_id,localDate,insert_data_batch.get(r).getDouble(2),create_user,create_user));
                                                                    }
                                                                }
                                                            }

                                                        }
                                                    }else {
                                                        //没有数据 数据库里面 则是全部新增
                                                        for (int r = 0 ; r<insert_data_batch.size();r++) {
                                                            Integer warehouse_id = Math.toIntExact(insert_data_batch.get(r).getLong(0));
                                                            Integer aggregation_id = Math.toIntExact(insert_data_batch.get(r).getLong(11));
                                                            LocalDate localDate = insert_data_batch.get(r).getLocalDate(12);
                                                            //不存在 数据库则需要判断是否在新增数据集合里面
                                                            if (mapList.size()==0){
                                                                Map<String,Object> map =new HashMap<>();
                                                                map.put("warehouse_id",warehouse_id);
                                                                map.put("aggregation_id",aggregation_id);
                                                                map.put("localDate",localDate);
                                                                map.put("supply_chain_goods_settle_package_spec",insert_data_batch.get(r).getDouble(2));
                                                                new_insert_data_batch2.add(Tuple.of(warehouse_id,aggregation_id,localDate,insert_data_batch.get(r).getDouble(2),create_user,create_user));
                                                            }else {
                                                                boolean have = false;
                                                                for (int u = 0 ; u < mapList.size(); u++){
                                                                    Map<String,Object> map = mapList.get(u);
                                                                    Integer warehouse_id3 =(Integer) map.get("warehouse_id");
                                                                    Integer aggregation_id3 =(Integer) map.get("aggregation_id");
                                                                    LocalDate localDate3 =(LocalDate) map.get("localDate");
                                                                    int eq = localDate3.compareTo(localDate3);
                                                                    double supply_chain_goods_settle_package_spec3 =(double)map.get("supply_chain_goods_settle_package_spec");
                                                                    BigDecimal bigDecimal3 =new BigDecimal(supply_chain_goods_settle_package_spec3);
                                                                    BigDecimal bigDecimal4 =new BigDecimal(insert_data_batch.get(r).getDouble(2));
                                                                    if (warehouse_id3==warehouse_id && aggregation_id3==aggregation_id  && bigDecimal3.compareTo(bigDecimal4)==0
                                                                            && eq==0){
                                                                        //有数据则不add
                                                                        have=true;
                                                                        break;
                                                                    }
                                                                }
                                                                if (!have){
                                                                    //不存在头的新增数据集合里面则可以add
                                                                    Map<String,Object> map =new HashMap<>();
                                                                    map.put("warehouse_id",warehouse_id);
                                                                    map.put("aggregation_id",aggregation_id);
                                                                    map.put("localDate",localDate);
                                                                    map.put("supply_chain_goods_settle_package_spec",insert_data_batch.get(r).getDouble(2));
                                                                    new_insert_data_batch2.add(Tuple.of(warehouse_id,aggregation_id,localDate,insert_data_batch.get(r).getDouble(2),create_user,create_user));
                                                                }
                                                            }
                                                        }
                                                    }
                                                    if(resJSArray.size()>0){
                                                        //有需要更新的数据
                                                        for (int r = 0 ; r<insert_data_batch.size();r++){
                                                            boolean f= false;
                                                            Integer a = Math.toIntExact(insert_data_batch.get(r).getLong(0));
                                                            Integer a1 = Math.toIntExact(insert_data_batch.get(r).getLong(1));
                                                            Integer a2 = Math.toIntExact(insert_data_batch.get(r).getLong(4));
                                                            String a4=insert_data_batch.get(r).getString(3);
                                                            BigDecimal bigDecimal2 =new BigDecimal(insert_data_batch.get(r).getDouble(2));
                                                            LocalDate localDate = insert_data_batch.get(r).getLocalDate(12);

                                                            for (int i = 0; i < resJSArray.size(); i++) {
                                                                JsonObject jsonObj = resJSArray.getJsonObject(i);
                                                                Integer warehouse_id = Integer.valueOf(jsonObj.getString("warehouse_id"));
                                                                Integer supply_chain_goods_id = Integer.valueOf(jsonObj.getString("supply_chain_goods_id"));
                                                                Integer supplier_id = Integer.valueOf(jsonObj.getString("supplier_id"));
                                                                double supply_chain_goods_settle_package_spec = Double.parseDouble(jsonObj.getString("supply_chain_goods_settle_package_spec"));
                                                                String goods_batch_code =jsonObj.getString("goods_batch_code");
                                                                String time_line_str =jsonObj.getString("time_line");
                                                                DateTimeFormatter df = DateTimeFormatter.ofPattern("yyyy-MM-dd");
                                                                LocalDate localDate2 = LocalDate.parse(time_line_str, df);
                                                                int eq = localDate.compareTo(localDate2);
                                                                if (a==warehouse_id && a1==supply_chain_goods_id && a2==supplier_id && a4.equals(goods_batch_code) && eq==0){
                                                                    BigDecimal bigDecimal1 =new BigDecimal(supply_chain_goods_settle_package_spec);
                                                                    if (bigDecimal1.compareTo(bigDecimal2)==0){
                                                                        //相等则是需要更新的数据
                                                                        update_data_batch.add(Tuple.of(insert_data_batch.get(r).getString(3),insert_data_batch.get(r).getLong(4),insert_data_batch.get(r).getDouble(5),
                                                                                insert_data_batch.get(r).getDouble(6),insert_data_batch.get(r).getDouble(7),insert_data_batch.get(r).getDouble(8),insert_data_batch.get(r).getDouble(9),
                                                                                insert_data_batch.get(r).getDouble(10),insert_data_batch.get(r).getLong(0),insert_data_batch.get(r).getLong(1),insert_data_batch.get(r).getDouble(2),insert_data_batch.get(r).getLocalDate(12)));
                                                                        f=true;
                                                                        break;
                                                                    }
                                                                }
                                                            }
                                                            if (!f){
                                                                new_insert_data_batch.add(insert_data_batch.get(r));
                                                            }

                                                        }
                                                    }else {
                                                        //没有需要更新的数据则是全部是新增
                                                        for (int y = 0 ;y<insert_data_batch.size();y++){
                                                            new_insert_data_batch.add(insert_data_batch.get(y));
                                                        }
                                                    }
                                                    //所有数据通过校验,执行insert or update 写入或更新数据
                                                    if (new_insert_data_batch.size() > 0) {
                                                        String insert_sql = "INSERT INTO initial_batch_detail(warehouse_id,supply_chain_goods_id, supply_chain_goods_settle_package_spec, goods_batch_code, supplier_id, " +
                                                                "real_settlement_price, transport_price, land_price, inventory_number, inventory_weight, inventory_land_acount,aggregation_id,time_line,create_user,update_user) VALUES($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11,$12,$13,$14,$15) RETURNING id";
                                                        Future<RowSet<Row>> insertInitialBatchDetialBatchFuture = this.commonRepository.insertOrUpdateBatch(conn, insert_sql, new_insert_data_batch, "insert_initial_batch_detial_batch");
                                                        futures2.add(insertInitialBatchDetialBatchFuture);
                                                        logger.info("insert_data_batch size: {}", new_insert_data_batch.size());
                                                    }
                                                    if (update_data_batch.size()>0){
                                                        String update_sql="update initial_batch_detail set goods_batch_code=$1,supplier_id=$2, " +
                                                                "  real_settlement_price=$3,transport_price=$4,land_price=$5,inventory_number=$6,inventory_weight=$7,inventory_land_acount=$8" +
                                                                " where warehouse_id=$9 and supply_chain_goods_id=$10 and  supply_chain_goods_settle_package_spec=$11 and  time_line=$12 ";
                                                        Future<RowSet<Row>> updateInitialBatchDetialBatchFuture = this.commonRepository.insertOrUpdateBatch(conn, update_sql, update_data_batch, "update_initial_batch_detial_batch");
                                                        futures2.add(updateInitialBatchDetialBatchFuture);
                                                        logger.info("update_data_batch size: {}", update_data_batch.size());
                                                    }
                                                    if (new_insert_data_batch2.size()>0){
                                                        String insert_sql = "INSERT INTO supplier_batch_goods_psi(warehouse_id,aggregation_id, time_line,supply_chain_goods_settle_package_spec,create_user,update_user ) VALUES($1, $2, $3, $4,$5,$6) RETURNING id";
                                                        Future<RowSet<Row>>  insertSupplierBatchGoodsPsiBatchFuture = this.commonRepository.insertOrUpdateBatch(conn, insert_sql, new_insert_data_batch2, "update_initial_batch_detial_batch");
                                                        futures2.add(insertSupplierBatchGoodsPsiBatchFuture);
                                                        logger.info("insert_tou_data_batch size: {}", new_insert_data_batch2.size());
                                                    }
                                                    if (futures2.size() > 0) {
                                                        logger.info("futures2.size(): {}", futures2.size());
                                                        CompositeFuture.join(futures2).onComplete(ar4 -> {
                                                            if (ar4.succeeded()) {
                                                                JsonObject insert_or_update_result = new JsonObject();
                                                                logger.info("insert_or_update_result: {}", insert_or_update_result.getString("msg"));
                                                                responseJSObj.put("msg", responseJSObj.getString("msg") + "; " + insert_or_update_result.getString("msg"));
                                                            } else {
                                                                responseJSObj.put("msg", responseJSObj.getString("msg") + "; " + ar4.cause().getMessage());
//                                                                promise.complete(responseJSObj);
                                                            }

                                                            //继续删除文件
                                                            vertx.fileSystem().delete(file_name, res0 -> {
                                                                if (res0.succeeded()) {
                                                                    logger.info("读取excel后删除文件 {} 成功", file_name);
                                                                    responseJSObj.put("code", 200).put("msg", responseJSObj.getString("msg") + "; 删除文件成功; 处理供应商信息完毕");
                                                                } else {
                                                                    logger.info("读取excel后删除文件 {} 失败 ", file_name);
                                                                    responseJSObj.put("code", 200).put("msg", responseJSObj.getString("msg") + "; 删除文件失败; 处理供应商信息完毕");
                                                                }
                                                                promise.complete(responseJSObj);
                                                            });
                                                        });
                                                    }
                                                    else {
                                                        //继续删除文件
                                                        vertx.fileSystem().delete(file_name, res0 -> {
                                                            if (res0.succeeded()) {
                                                                logger.info("读取excel后删除文件 {} 成功", file_name);
                                                                responseJSObj.put("code", 200).put("msg", responseJSObj.getString("msg") + "; 删除文件成功; 处理供应商信息完毕");
                                                            } else {
                                                                logger.info("读取excel后删除文件 {} 失败 ", file_name);
                                                                responseJSObj.put("code", 200).put("msg", responseJSObj.getString("msg") + "; 删除文件失败; 处理供应商信息完毕");
                                                            }
                                                            promise.complete(responseJSObj);
                                                        });
                                                    }
                                                }else {
                                                    responseJSObj.put("code",-1).put("msg", responseJSObj.getString("msg") + "; " + all1.cause().getMessage());
                                                    promise.complete(responseJSObj);
                                                }
                                        }).onFailure(err -> promise.fail(CommonUtils.createExceptionJsonObjectStr(err, "batchImportSupplierInfo", "查询进销存库存管理和初期库存明细数据失败！")));
                                //部分数据校验没有通过，全部不写入数据；返回提示信息
                                //继续删除文件
                                vertx.fileSystem().delete(file_name, res0 -> {
                                    if (res0.succeeded()) {
                                        logger.info("读取excel后删除文件 {} 成功", file_name);
                                        responseJSObj.put("code", 200).put("msg", responseJSObj.getString("msg") + "; 删除文件成功; 处理供应商信息完毕");
                                    } else {
                                        logger.info("读取excel后删除文件 {} 失败 ", file_name);
                                        responseJSObj.put("code", 200).put("msg", responseJSObj.getString("msg") + "; 删除文件失败; 处理供应商信息完毕");
                                    }
                                    promise.complete(responseJSObj);
                                });
                            }

                        } else {
                            promise.fail(CommonUtils.createExceptionJsonObjectStr(ar2, "batchImportSupplierInfo", "执行数据合法性校验发生错误"));
                        }
                    });
                } else { //没有通过数据字段非空校验
//                    responseJSObj.put("code", -1).put("msg", "没有通过非空校验");
                    promise.complete(responseJSObj);
                }
            } catch (Exception err) {  //end try ( OPCPackage p = OPCPackage.open(file_name, PackageAccess.READ)) {
                promise.fail(CommonUtils.createExceptionJsonObjectStr(err, "handleExcelFile in batchImportSupplierInfo", "打开excel文件报错"));
            }
        }).onFailure(err -> promise.fail(CommonUtils.createExceptionJsonObjectStr(err, "batchImportSupplierInfo", "数据库连接失败")));
        return promise.future();
    }

}
